﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace DataTier
{
    [Serializable]
    public class Org_Klagedata
    {
        private Org_Klagedata()
        {

        }

        #region Properties
        private int org_klagenr;

        public int OrgKlagenr
        {
            get { return org_klagenr; }
            set { org_klagenr = value; }
        }

        private string ejd_adresse;

        public string EjdAdresse
        {
            get { return ejd_adresse; }
            set { ejd_adresse = value; }
        }
        private string ejd_postnr;

        public string EjdPostnr
        {
            get { return ejd_postnr; }
            set { ejd_postnr = value; }
        }
        private string ejd_by;

        public string EjdBy
        {
            get { return ejd_by; }
            set { ejd_by = value; }
        }
        private EjdTypes ejd_type;

        public EjdTypes EjdType
        {
            get { return ejd_type; }
            set { ejd_type = value; }
        }

        private string ejd_kontakt;

        public string EjdKontakt
        {
            get { return ejd_kontakt; }
            set { ejd_kontakt = value; }
        }

        private KlageStatus status;

        public KlageStatus Status
        {
            get { return status; }
            set { status = value; }
        }
        private DateTime recieved;

        public DateTime Recieved
        {
            get { return recieved; }
            set { recieved = value; }
        }

        private string ejdNr;

        public string EjdNr
        {
            get { return ejdNr; }
            set { ejdNr = value; }
        }

        private string ejdKommuneNr;

        public string EjdKommuneNr
        {
            get { return ejdKommuneNr; }
            set { ejdKommuneNr = value; }
        }


        #endregion

        #region Load commands
        public static List<Org_Klagedata> LoadAll(string connStr)
        {
            return LoadAll(connStr, "");
        }

        public static Org_Klagedata Load(string connStr, int Org_Klagenr)
        {
            var toReturn = new Org_Klagedata();
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                try
                {
                    conn.Open();
                    var sqlStr = "select * from org_klagedata where org_klagenr = " + Org_Klagenr;
                    var sqlCommand = new SqlCommand(sqlStr, conn);
                    var sqlReader = sqlCommand.ExecuteReader();

                    if (sqlReader.Read())
                    {
                        toReturn.org_klagenr = int.Parse(sqlReader.GetInt16(0).ToString());
                        toReturn.ejd_adresse = sqlReader.GetString(1);
                        toReturn.ejd_postnr = sqlReader.GetString(2);
                        toReturn.ejd_by = sqlReader.GetString(3);
                        toReturn.ejd_type = Enums.GetEjdType(sqlReader.GetString(4));
                        toReturn.ejd_kontakt = sqlReader.GetString(5);
                        toReturn.status = Enums.GetKlageStatus(sqlReader.GetString(6));
                        toReturn.recieved = sqlReader.GetDateTime(7);
                        toReturn.ejdNr = sqlReader.GetString(8);
                        toReturn.EjdKommuneNr = sqlReader.GetString(9);
                    }
                }
                catch (SqlException ex)
                {
                    Console.WriteLine(ex.Message);
                    Console.WriteLine(ex.StackTrace);
                    throw new ArgumentException("Could not be found " + Org_Klagenr);
                }
                finally
                {
                    conn.Close();
                }
            }
            return toReturn;
        }

        public static List<Org_Klagedata> LoadAll(string connStr, KlageStatus status)
        {
            return LoadAll(connStr, "status = '" + Enum.GetName(typeof(KlageStatus), status) + "'");
        }

        public static List<Org_Klagedata> LoadAll(string connStr, string whereStatement)
        {
            var toReturn = new List<Org_Klagedata>();
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                try
                {
                    conn.Open();
                    var sqlStr = "select * from org_klagedata order by org_klagenr";
                    if (whereStatement.Length > 0)
                        sqlStr = "select * from org_klagedata where " + whereStatement + " order by org_klagenr";
                    var sqlCommand = new SqlCommand(sqlStr, conn);
                    var sqlReader = sqlCommand.ExecuteReader();

                    while (sqlReader.Read())
                    {
                        toReturn.Add(new Org_Klagedata
                        {
                            org_klagenr = int.Parse(sqlReader.GetInt16(0).ToString()),
                            ejd_adresse = sqlReader.GetString(1),
                            ejd_postnr = sqlReader.GetString(2),
                            ejd_by = sqlReader.GetString(3),
                            ejd_type = Enums.GetEjdType(sqlReader.GetString(4)),
                            ejd_kontakt = sqlReader.GetString(5),
                            status = Enums.GetKlageStatus(sqlReader.GetString(6)),
                            recieved = sqlReader.GetDateTime(7),
                            ejdNr = sqlReader.GetString(8),
                            ejdKommuneNr = sqlReader.GetString(9)

                        });

                    }
                }
                catch (SqlException ex)
                {
                    Console.WriteLine(ex.Message);
                    Console.WriteLine(ex.StackTrace);
                }
                finally
                {
                    conn.Close();
                }
            }
            return toReturn;

        }
        #endregion

        #region Insert commands

        public void Insert(string connStr)
        {
            Org_Klagedata.Insert(connStr, this.org_klagenr, this.ejd_adresse, this.ejd_postnr, this.ejd_by, this.ejd_type, this.ejd_kontakt, this.status, this.recieved, this.ejdNr, this.ejdKommuneNr);
        }

        public static Org_Klagedata Insert(string connStr,
            int org_klagenr,
            string ejd_adresse,
            string ejd_postnr,
            string ejd_by,
            EjdTypes ejd_type,
            string ejd_kontakt,
            KlageStatus status,
            string ejdNr,
            string ejdKommuneNr)
        {
            return Insert(connStr, org_klagenr, ejd_adresse, ejd_postnr, ejd_by, ejd_type,
                ejd_kontakt, status, DateTime.Now, ejdNr, ejdKommuneNr);
        }

        public static Org_Klagedata Insert(string connStr,
            int org_klagenr,
            string ejd_adresse,
            string ejd_postnr,
            string ejd_by,
            EjdTypes ejd_type,
            string ejd_kontakt,
            KlageStatus status,
            DateTime recieved,
            string ejdNr,
            string ejdKommuneNr)
        {
            var toReturn = new Org_Klagedata
            {
                org_klagenr = org_klagenr,
                ejd_adresse = ejd_adresse,
                ejd_postnr = ejd_postnr,
                ejd_by = ejd_by,
                ejd_type = ejd_type,
                ejd_kontakt = ejd_kontakt,
                status = status,
                recieved = recieved,
                ejdNr = ejdNr,
                ejdKommuneNr = ejdKommuneNr
            };
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                try
                {
                    conn.Open();
                    var sqlCommand = new SqlCommand("insert into org_klagedata (org_klagenr, ejd_adresse, ejd_postnr, ejd_by, ejd_type, ejer_kontakt, status, recieved, ejdNummer, ejdKommuneNr) values (@org_klagenr, @ejd_adresse, @ejd_postnr, @ejd_by, @ejd_type, @ejd_kontakt, @status, @recieved, @ejdNummer, @ejdKommuneNr)", conn);

                    sqlCommand.Parameters.Add("@org_klagenr", SqlDbType.SmallInt);
                    sqlCommand.Parameters.Add("@ejd_adresse", SqlDbType.VarChar, 250);
                    sqlCommand.Parameters.Add("@ejd_postnr", SqlDbType.VarChar, 50);
                    sqlCommand.Parameters.Add("@ejd_by", SqlDbType.VarChar, 50);
                    sqlCommand.Parameters.Add("@ejd_type", SqlDbType.VarChar, 50);
                    sqlCommand.Parameters.Add("@ejd_kontakt", SqlDbType.VarChar, 250);
                    sqlCommand.Parameters.Add("@status", SqlDbType.VarChar, 50);
                    sqlCommand.Parameters.Add("@recieved", SqlDbType.DateTime);
                    sqlCommand.Parameters.Add("@ejdNummer", SqlDbType.VarChar, 50);
                    sqlCommand.Parameters.Add("@ejdKommuneNr", SqlDbType.VarChar, 50);
                    
                    sqlCommand.Parameters["@org_klagenr"].Value = toReturn.org_klagenr;
                    sqlCommand.Parameters["@ejd_adresse"].Value = toReturn.ejd_adresse;
                    sqlCommand.Parameters["@ejd_postnr"].Value = toReturn.ejd_postnr;
                    sqlCommand.Parameters["@ejd_by"].Value = toReturn.ejd_by;
                    sqlCommand.Parameters["@ejd_type"].Value = Enum.GetName(typeof(EjdTypes), toReturn.ejd_type);
                    sqlCommand.Parameters["@ejd_kontakt"].Value = toReturn.ejd_kontakt;
                    sqlCommand.Parameters["@status"].Value = Enum.GetName(typeof(KlageStatus), toReturn.status);
                    sqlCommand.Parameters["@recieved"].Value = toReturn.recieved;
                    sqlCommand.Parameters["@ejdNummer"].Value = toReturn.ejdNr;
                    sqlCommand.Parameters["@ejdKommuneNr"].Value = toReturn.ejdKommuneNr;

                    var sqlReader = sqlCommand.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    Console.WriteLine(ex.Message);
                    Console.WriteLine(ex.StackTrace);
                }
                finally
                {
                    conn.Close();
                }
            }
            return toReturn;

        }


        #endregion

        public void Save(string connStr)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                try
                {
                    conn.Open();
                    var sqlCommand = new SqlCommand("update org_klagedata set ejd_adresse = @ejd_adresse, ejd_postnr = @ejd_postnr, ejd_by = @ejd_by, ejd_type = @ejd_type, ejer_kontakt = @ejer_kontakt, status = @status, recieved = @recieved, ejdNummer = @ejdNummer, ejdKommuneNr = @ejdKommuneNr where org_klagenr = @org_klagenr", conn);

                    sqlCommand.Parameters.Add("@ejd_adresse", SqlDbType.VarChar, 250);
                    sqlCommand.Parameters.Add("@ejd_postnr", SqlDbType.VarChar, 50);
                    sqlCommand.Parameters.Add("@ejd_by", SqlDbType.VarChar, 50);
                    sqlCommand.Parameters.Add("@ejd_type", SqlDbType.VarChar, 50);
                    sqlCommand.Parameters.Add("@ejer_kontakt", SqlDbType.VarChar, 250);
                    sqlCommand.Parameters.Add("@status", SqlDbType.VarChar, 50);
                    sqlCommand.Parameters.Add("@recieved", SqlDbType.DateTime);
                    sqlCommand.Parameters.Add("@ejdNummer", SqlDbType.VarChar, 50);
                    sqlCommand.Parameters.Add("@ejdKommuneNr", SqlDbType.VarChar, 50);
                    sqlCommand.Parameters.Add("@org_klagenr", SqlDbType.SmallInt);

                    sqlCommand.Parameters["@ejd_adresse"].Value = this.ejd_adresse;
                    sqlCommand.Parameters["@ejd_postnr"].Value = this.ejd_postnr;
                    sqlCommand.Parameters["@ejd_by"].Value = this.ejd_by;
                    sqlCommand.Parameters["@ejd_type"].Value = Enum.GetName(typeof(EjdTypes), this.ejd_type);
                    sqlCommand.Parameters["@ejer_kontakt"].Value = this.ejd_kontakt;
                    sqlCommand.Parameters["@status"].Value = Enum.GetName(typeof(KlageStatus), this.status);
                    sqlCommand.Parameters["@recieved"].Value = this.recieved;
                    sqlCommand.Parameters["@ejdNummer"].Value = this.ejdNr;
                    sqlCommand.Parameters["@ejdKommuneNr"].Value = this.ejdKommuneNr;
                    sqlCommand.Parameters["@org_klagenr"].Value = this.org_klagenr;

                    var sqlReader = sqlCommand.ExecuteNonQuery();
                }
                catch (SqlException ex)
                {
                    Console.WriteLine(ex.Message);
                    Console.WriteLine(ex.StackTrace);
                }
                finally
                {
                    conn.Close();
                }
            }
        }
    }


}
